import pandas as pd
import pymysql
import time

df = pd.read_excel('示例表格(1).xlsx', index_col=0, header=[0, 1, 2])
df = df.fillna(0)


def insert_mysql(sql):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
        print('successful')
    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()


def select_mysql(sqll):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sqll)
        # 只能有一个cursor.fetchall()
        result = cursor.fetchall()
        if result == ():
            return 'null'
        else:
            new_result = result[0][0]
            return new_result

    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()


def add_mysql(sqlll):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sqlll)
        # 只能有一个cursor.fetchall()
        result = cursor.fetchall()
        a = []
        for i in result:
            a.append(i[0][1:])
        value = int(max(a)) + 1
        return value
    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()


def solve_name(str1):
    old_list = str1.split()
    new_list = ' '.join(old_list)
    return new_list


def delete_mysql(sqllll):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sqllll)
        db.commit()
        return 'delete success'
    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()

print(delete_mysql('delete from main_table where name = "S01" and site="D1" and month = "6月"'))
